package org.light.utils;
import java.util.Iterator;
import java.util.Set;

import org.apache.commons.lang3.math.NumberUtils;
import org.apache.log4j.Logger;
import org.light.domain.Domain;
import org.light.domain.Dropdown;
import org.light.domain.Field;
import org.light.domain.ManyToMany;
import org.light.domain.Var;
import org.light.exception.ValidateException;

public class PgsqlReflector {
	protected static Logger logger = Logger.getLogger(PgsqlReflector.class);
	public static String generateTableDefinition(Domain domain) throws Exception{
		String result = "create table " + domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" (";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
	        Field f = (Field)it.next();
	        String fieldName = f.getFieldName();
	        String fieldType = f.getFieldType();
	        String fieldLengthStr = f.getLengthStr();
	        result += changeDomainFieldtoTableColumDefinitionToken(domain, fieldName, fieldType,fieldLengthStr)+",";
        }
        if (result.endsWith(",")) result = result.substring(0,result.length()-1);
        result += ");";
		return result;
	}

	public static String generateInsertSql(Domain domain) throws Exception{
		String result = "insert into " + domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" ";
		Iterator it = domain.getFieldsWithoutId().iterator();
		
		result += "( ";
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ ",";
        }
        result = result.substring(0,result.length()-1);
        result += ") values (";
		Iterator it2 = domain.getFieldsWithoutId().iterator();
        while (it2.hasNext()) {	
        	Field f = (Field)it2.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        result += "#{"+fieldName+"},";
        }
        result = result.substring(0,result.length()-1);
        result += ")";
        return result;
	}
	
	public static String generateInsertLinkTwoSql(Domain master,Domain slave) throws Exception{
			String result = "insert into " +TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +" ";	
			result += "( ";
			result += StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id");
			result += ",";
			if (StringUtil.isBlank(slave.getAlias())) {
				result += StringUtil.changeDomainFieldtoTableColum(slave.getCapFirstDomainName()+"Id");
			}else {
				result += StringUtil.changeDomainFieldtoTableColum(StringUtil.capFirst(slave.getAlias())+"Id");
			}
	        result += ") values (#{"+master.getLowerFirstDomainName()+"Id},#{"+StringUtil.lowerFirst(slave.getAlias())+"Id})";
	        return result;
		
	}
	
	public static String generateDeleteLinkTwoSql(Domain master,Domain slave) throws Exception{
		String result = "delete from " +TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +" ";	
		result += " where ";
		result += StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id");
		result += " = #{"+master.getLowerFirstDomainName()+"Id} and ";
		if (StringUtil.isBlank(slave.getAlias())){
			result += StringUtil.changeDomainFieldtoTableColum(slave.getCapFirstDomainName()+"Id");
		} else {
			result += StringUtil.changeDomainFieldtoTableColum(StringUtil.capFirst(slave.getAlias())+"Id");
		}
        result += " = #{"+StringUtil.lowerFirst(slave.getAlias())+"Id}";
        return result;
	}
	
	public static String generateUpdateSql(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" set ";
		Iterator it = domain.getFieldsWithoutId().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        if (!isPrimaryKey(domain, fieldName, fieldType)) {
	        	result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ " = #{"+fieldName+"} ,";
	        }
        }
        result = result.substring(0,result.length()-1);
        result += generatePrimaryWhereParamSqlTokenWithDomainId(domain);
        return result;
	}
	
	public static String generateDeleteSqlWithValue(Domain domain) throws Exception{
		String result = "delete from " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " ";
        result += generatePrimaryWhereParamSqlTokenWithDomainId(domain);
        return result;
	}

	public static String generateSoftDeleteSqlWithValue(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " set " + StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName()) + " = "+domain.getDomainDeletedStr()+" where ";
		result += StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+ " = #{value} ";
        return result;
	}
	
	public static String generateActivateSqlWithValue(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " set " + StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName()) + " = "+domain.getDomainActiveStr()+" where ";
		result += StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+ " = #{value} ";
        return result;
	}
	
	public static String changeDomainFieldtoTableColumDefinitionToken(Domain domain, String fieldName, String fieldType, String fieldLengthStr){
		String result = StringUtil.changeDomainFieldtoTableColum(fieldName) + " ";
		if (isPrimaryKey(domain,fieldName,fieldType)){
			result += "serial primary key";
		}else if (isDoaminIdOrDomainNameOrActiveField(domain,fieldName,fieldType)){
			result += lookupPgSqlType(fieldName,fieldType,fieldLengthStr) + " not null ";
		}else {
			result += lookupPgSqlType(fieldName,fieldType,fieldLengthStr) + " null ";			
		}
		return result;
	}
	
	public static boolean isDoaminIdOrDomainNameOrActiveField(Domain domain,String fieldName,String fieldType){
		if (domain==null||domain.isLegacy()) return false;
		if ((fieldType.equalsIgnoreCase("boolean")&&fieldName.equals(domain.getActive().getFieldName()))||(fieldType.equalsIgnoreCase("string")&&fieldName.equals(domain.getDomainName().getFieldName()))){
			return true;
		}
		return false;
	}
	
	
	public static boolean isPrimaryKey(Domain domain,String fieldName,String fieldType){
		boolean retVal = false;
		if (domain.getDomainId()==null) return false;
		if ("long".equalsIgnoreCase(fieldType)||"bigint".equalsIgnoreCase(fieldType)||"int".equalsIgnoreCase(fieldType)||"Integer".equalsIgnoreCase(fieldType)){
			if ("id".equalsIgnoreCase(fieldName)||fieldName.equalsIgnoreCase(domain.getStandardName()+"id")||fieldName.equalsIgnoreCase(domain.getDomainId().getFieldName())){
				retVal =true;
			}
		}
		return retVal;
	}
	
	public static String generatePrimaryKeySqlToken(Domain domain){
		String result = "";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        if (isPrimaryKey(domain, fieldName, fieldType)){
	        	result =  "primary key (" + StringUtil.changeDomainFieldtoTableColum(fieldName) +")";
	        }
        }
        return result;
	}
	
	public static String generatePrimaryWhereParamSqlTokenWithDomainId(Domain domain){
		String result = "";
		Iterator it = domain.getFields().iterator();
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
	        if (isPrimaryKey(domain, fieldName, fieldType)){
	        	result = "where " +  StringUtil.changeDomainFieldtoTableColum(fieldName) +" = #{"+domain.getDomainId().getLowerFirstFieldName()+"}";
	        }
        }
        return result;
	}
	
	public static String lookupPgSqlType(String fieldName, String fieldType,String fieldLengthStr){
		String result = "";
		if (fieldType.equalsIgnoreCase("long")) {
			result = "BigInt";
		}
		if  (fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")) {
			result = "Integer";
		}
		if  (fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")) {
			result = "real";
		}
		if  (fieldType.equalsIgnoreCase("BigDecimal")||fieldType.equalsIgnoreCase("decimal")) {
			result = "Numeric";
		}
		if  (fieldType.equalsIgnoreCase("boolean")) {
			result = "bool";
		}
		if  (fieldType.equalsIgnoreCase("image")||fieldType.equalsIgnoreCase("byte []")) {
			result = "bytea";
		}
		if  (fieldType.equalsIgnoreCase("String")) {
			if (fieldName.toLowerCase().contains("comment")||fieldName.toLowerCase().contains("description")||fieldName.toLowerCase().contains("content")){
				result = "text";
			}else if (!StringUtil.isBlank(fieldLengthStr)) {
				result = "varchar("+fieldLengthStr+")";
			}else {
				result = "varchar(255)";
			}
		}
		return result; 
	}
	
	public static String generateSelectAllStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain);
        if (domain.getDomainId()!=null) result += " order by " + StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName()) + " asc";
		return result;
	}
	
	public static String generateSelectAllStatementWithDeniedFields(Domain domain,Set<Field> deniedFields) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFieldsWithDeniedFields(domain,deniedFields) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain);
        if (domain.getDomainId()!=null) result += " order by " + StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName()) + " asc";
		return result;
	}

	public static String generateSelectAllByLimitStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" limit #{limit} offset #{start}";
        return result;
	}
	
	static String generateSelectAllByLimitStatement(Domain domain, Var start, Var length) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" limit #{"+start.getVarName()+"},#{"+length.getVarName()+"};";
        return result;
	}
	
	public static String generateCountRecordStatement(Domain domain, Var countNum) throws Exception{
		String result = "select count("+ StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName()) + ") as "+countNum.getVarName()+" from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +";";
        return result;
	}
	
	public static String generateSelectByFieldStatement(Domain domain, Field field) throws Exception{
		String result = "select "+  DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+ StringUtil.changeDomainFieldtoTableColum(field.getFieldName())+" = #{"+field.getFieldName()+"};";
        return result;
	}
	
	public static String generateSelectActiveStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName())+" = "+domain.getDomainActiveStr()+";";
        return result;
	}
	
	public static String generateFindByIdStatement(Domain domain) throws Exception{
		String result = "select "+DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+" = #{"+domain.getDomainId().getFieldName()+"};";
        return result;
	}
	
	public static String generateFindByNameStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" = #{"+domain.getDomainName().getFieldName()+"};";
        return result;
	}
	
	public static String generateSearchByNameStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" like #{"+domain.getDomainName().getFieldName()+"}";
        return result;
	}
	
	public static String generateSearchByNameUsingValueStatement(Domain domain) throws Exception{
		String result = "select "+ DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(domain.getDomainName().getFieldName())+" like '%'||#{value})||'%'";
        return result;
	}
	
	public static String generateSearchByDescriptionStatement(Domain domain, Field description) throws Exception{
		String result = "select " +DomainTokenUtil.generateTableCommaFields(domain) + " from "+ domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) +" where "+StringUtil.changeDomainFieldtoTableColum(description.getFieldName())+" like "+StringUtil.changeDomainFieldtoTableColum(description.getFieldName())+";";
        return result;
	}
	
	public static String generateToggleSqlWithValue(Domain domain) throws Exception{
		String result = "update " +domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain) + " set " + StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName()) + " = not "+ StringUtil.changeDomainFieldtoTableColum(domain.getActive().getFieldName())+" where ";
		result += StringUtil.changeDomainFieldtoTableColum(domain.getDomainId().getFieldName())+ " = #{value} ";
        return result;
	}
	
	public static String generateSelectActiveUsingMasterIdStatement(Domain master,Domain slave) throws Exception{
		String result = "";
		if (StringUtil.isBlank(slave.getAlias())) {
			result = "select distinct "+ DomainTokenUtil.generateTableCommaFieldsWithTablePrefix(slave) + " from "+ TableStringUtil.domainNametoTableNameWithDbPrefix(slave)+ ","+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+" where " + TableStringUtil.domainNametoTableNameWithDbPrefix(slave) +"."+slave.getDomainId().getFeildNameAsTableColumn()+" = "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+"."+StringUtil.changeDomainFieldtoTableColum(slave.getCapFirstDomainName()+"Id")+
						" and "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +"."+StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id") +" = #{value} and "+TableStringUtil.domainNametoTableNameWithDbPrefix(slave) + "." + slave.getActive().getFeildNameAsTableColumn() + " = "+slave.getDomainActiveStr();
		}else {
			result = "select distinct "+ DomainTokenUtil.generateTableCommaFieldsWithTablePrefix(slave) + " from "+ TableStringUtil.domainNametoTableNameWithDbPrefix(slave)+ ","+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+" where " + TableStringUtil.domainNametoTableNameWithDbPrefix(slave) +"."+slave.getDomainId().getFeildNameAsTableColumn()+" = "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave)+"."+StringUtil.changeDomainFieldtoTableColum(StringUtil.capFirst(slave.getAlias())+"Id")+
					" and "+TableStringUtil.twoDomainNametoTableNameWithDbPrefix(master, slave) +"."+StringUtil.changeDomainFieldtoTableColum(master.getCapFirstDomainName()+"Id") +" = #{value} and "+TableStringUtil.domainNametoTableNameWithDbPrefix(slave) + "." + slave.getActive().getFeildNameAsTableColumn() + " = "+slave.getDomainActiveStr();
		}
		return result;
	}
	
	public static String generatePgsqlDropTableStatement(Domain domain) throws Exception{
		String result = "DROP TABLE  if exists "+domain.getDbPrefix() + TableStringUtil.domainNametoTableName(domain)+";";
		return result;
	}
	
	public static String generateInsertSqlWithValue(Domain domain) throws Exception{
		String result = "insert into " + domain.getDbPrefix() +TableStringUtil.domainNametoTableName(domain) +" ";
		Iterator it = domain.getFields().iterator();
		
		result += "( ";
        while (it.hasNext()) {	
        	Field f = (Field)it.next();
 	        String fieldName = f.getFieldName();
 	        String fieldType = f.getFieldType();
 	        if (f instanceof Dropdown) result += StringUtil.changeDomainFieldtoTableColum(((Dropdown)f).getAliasName())+ ",";
 	        else result += StringUtil.changeDomainFieldtoTableColum(fieldName)+ ",";
        }
        result = result.substring(0,result.length()-1);
        result += ") values (";
		Iterator it2 = domain.getFields().iterator();
        while (it2.hasNext()) {	
        	Field f = (Field)it2.next();
        	String fieldValue = "";
			String fieldName = "";
			String fieldType = "";
        	if (f instanceof Dropdown) {
        		Dropdown dp = (Dropdown)f;
        		fieldValue = dp.getFieldValue();
     	        fieldName = dp.getAliasName();
     	        fieldType = dp.getFieldType();
        	}else if (f.getFieldType().equalsIgnoreCase("boolean")){
     	        fieldValue = BooleanUtil.parseBooleanNoNull(f.getFieldValue()).toString();
     	        fieldName = f.getFieldName();
     	        fieldType = "boolean";
        	}else {
     	        fieldValue = f.getFieldValue();
     	        fieldName = f.getFieldName();
     	        fieldType = f.getFieldType();
        	}        	

 	        try {
	 	        if (!StringUtil.isBlank(fieldValue)&&NumberUtils.isNumber(fieldValue)){
	 	        	if (Double.valueOf(fieldValue) - Math.round(Double.valueOf(fieldValue)) < 0.00005){
	 	        		fieldValue = "" + Math.round(Double.valueOf(fieldValue));
	 	        		logger.debug("JerryDebug:roundtoint:"+fieldValue);
	 	        	}
	 	        }
 	        } catch (Exception e){
 	        	e.printStackTrace();
 	        	throw new ValidateException("数据项"+fieldName+"类型定义错误！");
 	        }
 	       if ((StringUtil.isBlank(fieldValue)&&f instanceof Dropdown)|| fieldType.equalsIgnoreCase("image")) {
	        	result +=  "null,";
 	       }else  if (!StringUtil.isBlank(fieldType)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double")||fieldType.equalsIgnoreCase("boolean"))) {
 	    	   if (StringUtil.isBlank(fieldValue)&&(fieldType.equalsIgnoreCase("int")||fieldType.equalsIgnoreCase("Integer")||fieldType.equalsIgnoreCase("long")||fieldType.equalsIgnoreCase("float")||fieldType.equalsIgnoreCase("double"))) result += "0,";
 	    	   else result +=  fieldValue + ",";
	        } else {
	        	result += "'"+ fieldValue + "',";
	        }
        }
        result = result.substring(0,result.length()-1);
        result += ");";
        return result;
	}
	
	public static String generateMtmInsertSqlWithValues(ManyToMany mtm) throws Exception{
		if (StringUtil.isBlank(mtm.getValues())||mtm.getSlave().isLegacy()) return "";
		else {
		String [] slaveValues = mtm.getValues().split(",");
			String result = "";
				for (String slaveValue:slaveValues) {
				    result = result + "insert into " +  mtm.getMaster().getDbPrefix() + StringUtil.changeDomainFieldtoTableColum(mtm.getMaster().getStandardName()+StringUtil.capFirst(mtm.getSlaveAlias())) +" ";
					result = result + "("+StringUtil.changeDomainFieldtoTableColum(mtm.getMaster().getStandardName()+"Id")+" , "+StringUtil.changeDomainFieldtoTableColum(mtm.getSlaveAlias()+"Id")+") values ( ";		
			        if (TypeUtil.isNumeric(mtm.getMaster().getDomainId().getFieldRawType())){
			        	result = result + mtm.getMaster().getDomainId().getFieldValue() + "," ;
			        }else {
			        	result = result +"'"+ mtm.getMaster().getDomainId().getFieldValue() + "',"; 
			        }
			        if (TypeUtil.isNumeric(mtm.getSlave().getDomainId().getFieldRawType())){
			        	result = result + slaveValue ;
			        }else {
			        	result = result +"'"+ slaveValue + "'"; 
			        }
			        result += ");\n";
				} 
			return result;
		}
	}

	public static String generateSetSerialVal10000(Domain d) throws Exception{
		try {
			StringBuilder sb = new StringBuilder("select setval('");
			sb.append(generatePgsqlIdSerialName(d)).append("',10000);");
			return sb.toString();
		} catch (Exception e) {
			e.printStackTrace();
			return "";
		}
	}

	private static String generatePgsqlIdSerialName(Domain d) throws Exception{
		String tableName = d.getDbPrefix() + TableStringUtil.domainNametoTableName(d);
		if (d.getDomainId() == null) throw new ValidateException("域对象没有主键！");
		String fieldName = StringUtil.changeDomainFieldtoTableColum(d.getDomainId().getFieldName());
		return tableName+"_"+fieldName+"_seq";
	}
}
